﻿/*
 * Created by SharpDevelop.
 * User: V.I.Evgenyev
 * Date: 14.09.2010
 * Time: 12:22
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */

 /* Created by SharpDevelop.
 * User: Amig0
 * Date: 03.09.2010
 * Time: 22:16
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Drawing;
using System.IO;
using System.Windows.Forms;
using System.Configuration; 

namespace TreeDB
{
	/// <summary>
	/// Description of MainForm.
	/// </summary>
	public partial class MainForm : Form
	{

//-------Выбор (поиск) узла дерева по его ID ------------------------------------------------------
		TreeNode FindNode (int select_key)
		{
			TreeNode[] find_node = treeView1.Nodes.Find (select_key.ToString(), true);
			return find_node [0];
		}
//-------Обновление данных в БД из rtf поля--------------------------------------------------------
		void update_rtf_data (int id)
		{
			MemoryStream ms = new MemoryStream();
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + db_file_name.Value);
     		cnn.Open();
     		
 			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
			SQLiteParameter ID = new SQLiteParameter("@ID");
			SQLiteParameter rtf_data = new SQLiteParameter("@rtf_data");
			SQLcommand.CommandText = "UPDATE rtftable SET rtf_data = @rtf_data WHERE ID = @ID";
			SQLcommand.Parameters.Add(ID);   
			SQLcommand.Parameters.Add(rtf_data);
						
			ID.Value = id;
			rtf_data.Value = htmlEditorControl1.BodyHtml.ToString();
			SQLcommand.ExecuteNonQuery();
			
			cnn.Close();	
			
		}
//-----------------добавление узла (основная функция)----------------------------------------------
		void add_new_item (int place, int type_ico)
		{
			// place: 0 - as next 1 - as child
			// type_ico 1 - document 2 - folder
			
			int parent_node = 0;
			int newID = 0;
			newID = max_id () + 1;
			
			if (place == 1)
			{
				parent_node =	Convert.ToInt32 (treeView1.SelectedNode.Name);
			} else
			{
				parent_node = parent_ID (Convert.ToInt32 (treeView1.SelectedNode.Name), db_file_name.Value);
				
			}

			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + db_file_name.Value);
     		cnn.Open();
     		
 			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
 			
			SQLiteParameter ID = new SQLiteParameter("@ID");
			SQLiteParameter ID_parent = new SQLiteParameter("@ID_parent");
			SQLiteParameter ID_icon = new SQLiteParameter("@ID_icon");
			SQLiteParameter ID_name = new SQLiteParameter("@ID_name");
			
			SQLcommand.CommandText = "INSERT INTO maintable (ID, ID_parent, ID_icon, ID_name) VALUES(@ID, @ID_parent, @ID_icon, @ID_name)";
			
			SQLcommand.Parameters.Add(ID);
			SQLcommand.Parameters.Add(ID_parent);
			SQLcommand.Parameters.Add(ID_icon);
			SQLcommand.Parameters.Add(ID_name);
						
			ID.Value = newID;
			ID_name.Value = "Node" + newID.ToString();
			ID_icon.Value = type_ico;
			ID_parent.Value = parent_node;
			SQLcommand.ExecuteNonQuery();
			
			SQLcommand.CommandText = "INSERT INTO rtftable (ID) VALUES(@ID)";
			SQLcommand.Parameters.Add(ID);
			ID.Value = newID;
			SQLcommand.ExecuteNonQuery();
			
			cnn.Close();
			if (parent_node == 0)
			{
				treeView1.Nodes.Add (newID.ToString(), "Node" + newID.ToString(),type_ico );
				treeView1.SelectedNode = FindNode (newID);
			}
			else
			{
				FindNode (parent_node).Nodes.Add (newID.ToString(), "Node" + newID.ToString(),type_ico );
				treeView1.SelectedNode = FindNode (newID);
			}
			// очистка поля заметок
			htmlEditorControl1.BodyHtml = "";

			
		}
//-------Чтение данных Note и attrtf из БД---------------------------------------------------------
		void read_data (string id)
		{
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + db_file_name.Value);
     		cnn.Open();
 			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
 			SQLiteParameter ID = new SQLiteParameter("@id");
				
			SQLcommand.CommandText = "SELECT rtf_data FROM rtftable WHERE ID = @id";
			SQLcommand.Parameters.Add(ID);
			ID.Value = id;
			SQLiteDataReader read_data = SQLcommand.ExecuteReader ();
			while (read_data.Read ())
				{
				try
					{
			    		htmlEditorControl1.BodyHtml = read_data.GetString(0);
 					}
				catch
					{
				// данных нет (пустая заметка)					
					htmlEditorControl1.BodyHtml = " ";
					}
				}
	
			SQLiteCommand SQLcommand2 = new SQLiteCommand(cnn);
			SQLcommand2.CommandText = "SELECT ID_file_name, ID_file FROM filenametable WHERE ID = @id";
			SQLcommand2.Parameters.Add(ID);
			ID.Value = id;
			SQLiteDataReader read_data2 = SQLcommand2.ExecuteReader ();
			listView1.Clear();
			try
			{
				while (read_data2.Read ())
				{
					string fileName = read_data2.GetString (0);
					
					ListViewItem itm = listView1.Items.Add(fileName,7);
					itm.Name = read_data2.GetInt32 (1).ToString();
				}
		
			}
			catch
			{
				listView1.Clear();
			}
			
			cnn.Close();
					
		}
//-------Поиск максимального ID--------------------------------------------------------------------
		int max_id( )
		{
			int result;
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + db_file_name.Value);
     		cnn.Open();
 			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
 			SQLcommand.CommandText = "SELECT max(ID) FROM maintable";
 			SQLiteDataReader read_data = SQLcommand.ExecuteReader ();
			read_data.Read ();
			result = read_data.GetInt16(0);
			cnn.Close();
			
			return result;
		}
//-------Поиск максимального ID_file---------------------------------------------------------------
		int max_id_file( )
		{
			int result = 0;
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + db_file_name.Value);
     		cnn.Open();
 			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
 			SQLcommand.CommandText = "SELECT max(ID_file) FROM filenametable";
 			SQLiteDataReader read_data = SQLcommand.ExecuteReader ();
			read_data.Read ();
 			result = read_data.GetInt16(0);
 			cnn.Close();
			return result;
		}	
//-------Построение дерева из БД-------------------------------------------------------------------
		void treeDB_open (string DB_name)
		{
			
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + DB_name);
     		cnn.Open();
 			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
 			SQLcommand.CommandText = "SELECT ID, ID_PARENT, ID_icon, ID_name FROM maintable ORDER BY ID";
 			SQLiteDataReader read_data = SQLcommand.ExecuteReader ();
			treeView1.Nodes.Clear();

 			while (read_data.Read ()==true)
 				{
 				if (read_data.GetInt32(1)==0)
 					{
 					treeView1.Nodes.Add (read_data.GetInt32(0).ToString() , read_data.GetString(3),read_data.GetInt32 (2));
  					} else
 						{
 							TreeNode PNode = FindNode (read_data.GetInt32(1));
 							PNode.Nodes.Add (read_data.GetInt32(0).ToString() , read_data.GetString(3),read_data.GetInt32 (2));
 						}
 				}
 			cnn.Close();
 			treeView1.CollapseAll();
 	 			
		}
//-------Сохранение rtf в БД-----------------------------------------------------------------------
		void treeDB_save (string DB_name)
		{
			TreeNode save_node = treeView1.SelectedNode;
 			int ID = Convert.ToInt32 (save_node.Name);
 			update_rtf_data (ID);
 		}
//-------Редактирование имени ноды-----------------------------------------------------------------
		void DB_name_edit (string id, string id_name, string DB_name)
			{
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + DB_name);
     		cnn.Open();
     		
 			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
			SQLiteParameter ID = new SQLiteParameter("@ID");
			SQLiteParameter ID_name = new SQLiteParameter("@ID_name");
			SQLcommand.CommandText = "UPDATE maintable SET ID_name = @ID_name WHERE ID = @ID";
			SQLcommand.Parameters.Add(ID);   
			SQLcommand.Parameters.Add(ID_name);
						
			ID.Value = id;
			ID_name.Value = id_name;
			SQLcommand.ExecuteNonQuery();
			
			cnn.Close();	
			}
//-------Поиск ID родителя узла--------------------------------------------------------------------
		int parent_ID (int id, string DB_name)
{
			int result;
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + DB_name);
     		cnn.Open();
     		
 			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
 			
 			SQLiteParameter ID = new SQLiteParameter("@ID");
			SQLcommand.CommandText = "SELECT ID_parent FROM maintable WHERE ID = @ID";
			SQLcommand.Parameters.Add(ID);
 			ID.Value = id;
 			
 			SQLiteDataReader read_data = SQLcommand.ExecuteReader ();
 			read_data.Read ();
			result = read_data.GetInt16(0);
						                 
			cnn.Close();
			
			return result;	
		
}
//-----------создание новой БД---------------------------------------------------------------------
		void create_DB ( )
		{
			string baseName = "NewDB.3db";
			SQLiteConnection.CreateFile (baseName);
			SQLiteConnection cnn = new SQLiteConnection ("Data Source = " + baseName);
			cnn.Open();
			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
			
			// ID_name = имя узла, ID_icon = иконка узла, ID_parent = узел-родитель, ID = номер узла
			SQLcommand.CommandText = "CREATE TABLE maintable (ID_name TEXT, ID_icon INTEGER, ID_parent INTEGER, ID INTEGER)";
			SQLcommand.ExecuteNonQuery();
			
			// rtf_data = rtf-текст, ID = номер узла
			SQLcommand.CommandText = "CREATE TABLE rtftable (rtf_data BLOB, ID INTEGER)";
			SQLcommand.ExecuteNonQuery();
			
			// ID_file_name = имя файла, ID_file_icon = иконка для файла, ID_file = номер файла, ID = номер узла
			SQLcommand.CommandText = "CREATE TABLE filenametable (ID_file_name TEXT, ID_file_icon BLOB, ID_file INTEGER, ID INTEGER)";
			SQLcommand.ExecuteNonQuery();
			
			// ID_file_data = массив данных файла, ID_file = номер файла
			SQLcommand.CommandText = "CREATE TABLE filedatatable (file_data BLOB, ID_file INTEGER)";
			SQLcommand.ExecuteNonQuery();
			
			SQLcommand.CommandText = "INSERT INTO maintable (ID, ID_parent, ID_icon, ID_name) VALUES(1, 0, 1, 'Node1')";
			SQLcommand.ExecuteNonQuery();
			
			SQLcommand.CommandText = "INSERT INTO rtftable (ID) VALUES(1)";
			SQLcommand.ExecuteNonQuery();
			
			SQLcommand.CommandText = "INSERT INTO filenametable (ID_file) VALUES(0)";
			SQLcommand.ExecuteNonQuery();
			
	
			
			cnn.Close();
			
		}
//--------------------удаление узла со всей информацией--------------------------------------------
		void DB_delete_node (int id)
		{
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + db_file_name.Value);
     		cnn.Open();
     		
 			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
 			SQLiteParameter ID = new SQLiteParameter("@ID");
			SQLcommand.CommandText = "SELECT ID FROM maintable WHERE ID_parent = @ID";
			SQLcommand.Parameters.Add(ID);
 			ID.Value = id;
 			SQLiteDataReader read_data = SQLcommand.ExecuteReader ();
 			if (read_data.Read ()==true)
 			{
 				MessageBox.Show("Can not delete parent node.", "Node Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
 				read_data.Close();
 			} else
 			{
 				read_data.Close();
 				string message = "Do you really want to delete a node?";
                string caption = "Node delete";
				MessageBoxButtons buttons = MessageBoxButtons.YesNo;
				DialogResult result;
				result = MessageBox.Show(message, caption, buttons);
				if (result == System.Windows.Forms.DialogResult.Yes)
					{
					treeView1.Nodes.Remove(treeView1.SelectedNode);
	
					
					SQLcommand.CommandText = "DELETE FROM filedatatable WHERE ID_file IN (SELECT ID_file FROM filenametable WHERE ID =  @ID)";
					SQLcommand.Parameters.Add(ID);
					ID.Value = id;
					SQLcommand.ExecuteNonQuery();
					
					SQLcommand.CommandText = "DELETE FROM filenametable WHERE ID = @ID";
					SQLcommand.Parameters.Add(ID);
					ID.Value = id;
					SQLcommand.ExecuteNonQuery();
					
			
					SQLcommand.CommandText = "DELETE FROM maintable WHERE ID = @ID";
					SQLcommand.Parameters.Add(ID);
					ID.Value = id;
					SQLcommand.ExecuteNonQuery();
					
					SQLcommand.CommandText = "DELETE FROM rtftable WHERE ID = @ID";
					SQLcommand.Parameters.Add(ID);
					ID.Value = id;
					SQLcommand.ExecuteNonQuery();
					
					
					}
 			}
 			cnn.Close();
			
		}
//------------------------чтение файла конфигурации------------------------------------------------
		void start_app_config ()
			{
				try
				{
				System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); 
				AppSettingsSection appSettings = (AppSettingsSection)config.GetSection("appSettings");
				int X = Convert.ToInt32(config.AppSettings.Settings ["LocationX"].Value);
				int Y = Convert.ToInt32(config.AppSettings.Settings ["LocationY"].Value);
				int W = Convert.ToInt32(config.AppSettings.Settings ["SizeWidth"].Value);
				int H = Convert.ToInt32(config.AppSettings.Settings ["SizeHeight"].Value);
				MainForm.ActiveForm.Location = new Point (X, Y);
				MainForm.ActiveForm.Width = W;
				MainForm.ActiveForm.Height = H;
				db_file_name.Value = config.AppSettings.Settings ["File"].Value.ToString();
				string message = "Do you want open file: " + db_file_name.Value + "?";
                string caption = "Open DataBase";
				MessageBoxButtons buttons = MessageBoxButtons.YesNo;
				DialogResult result;
				result = MessageBox.Show(message, caption, buttons);
				if (result == System.Windows.Forms.DialogResult.Yes)
					{
					treeDB_open (db_file_name.Value);
					treeView1.SelectedNode = FindNode (Convert.ToInt32(config.AppSettings.Settings ["Node select"].Value));
					treeView1.Select ();
					
					
					}
		
				}
				catch
				{
		
				}
								
			}
//-------------сохранение файла конфигурации-------------------------------------------------------
		void  save_app_config ()
			{
			try
				{
				System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); 
				AppSettingsSection appSettings = (AppSettingsSection)config.GetSection("appSettings");
				appSettings.Settings["LocationX"].Value = MainForm.ActiveForm.Location.X.ToString();
				appSettings.Settings["LocationY"].Value = MainForm.ActiveForm.Location.Y.ToString();
				appSettings.Settings["SizeWidth"].Value = MainForm.ActiveForm.Width.ToString();
				appSettings.Settings["SizeHeight"].Value = MainForm.ActiveForm.Height.ToString();
				appSettings.Settings["File"].Value = db_file_name.Value;
				appSettings.Settings["Node select"].Value = treeView1.SelectedNode.Name.ToString();
				config.Save(); 
				}
				catch
				{
					
				}
	
			}
//------------установка иконки узла----------------------------------------------------------------
		void set_icon (string id, int icon)
		{
			treeView1.SelectedNode.ImageIndex = icon;
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + db_file_name.Value.ToString());
     		cnn.Open();
     		
 			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
			SQLiteParameter ID = new SQLiteParameter("@ID");
			SQLiteParameter ID_icon = new SQLiteParameter("@ID_icon");
			SQLcommand.CommandText = "UPDATE maintable SET ID_icon = @ID_icon WHERE ID = @ID";
			SQLcommand.Parameters.Add(ID);   
			SQLcommand.Parameters.Add(ID_icon);
						
			ID.Value = id;
			ID_icon.Value = icon;
			SQLcommand.ExecuteNonQuery();
			
			cnn.Close();	
		}
//----проверка узла на наличие потомков------------------------------------------------------------
		bool ID_check (string id)
		{
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + db_file_name.Value.ToString());
     		cnn.Open();
     		
 			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
 			
 			SQLiteParameter ID = new SQLiteParameter("@ID");
			SQLcommand.CommandText = "SELECT ID FROM maintable WHERE ID_parent = @ID";
			SQLcommand.Parameters.Add(ID);
 			ID.Value = id;
 			SQLiteDataReader read_data = SQLcommand.ExecuteReader ();
 			if (read_data.Read ()==true)
 				{
 				MessageBox.Show("Can not move parent node.", "Node Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
 				read_data.Close();
 				return false;
 				} else
 					{
 					read_data.Close();
 					return true;
 					}
 			
		}
//----перемещение узлов----------------------------------------------------------------------------
		void Move_node (string nodesource, string nodetarget, int newID)
		{
			
						
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + db_file_name.Value);
     		cnn.Open();
     		SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
 			
			SQLiteParameter ID = new SQLiteParameter("@ID");
			SQLiteParameter ID_Source = new SQLiteParameter("@ID_Source");
			SQLiteParameter ID_Target = new SQLiteParameter("@ID_Target");
			
			SQLcommand.CommandText = "UPDATE maintable SET ID = @ID, ID_parent = @ID_Target WHERE ID = @ID_Source";
			SQLcommand.Parameters.Add(ID);
			SQLcommand.Parameters.Add(ID_Source);
			SQLcommand.Parameters.Add(ID_Target);
			ID.Value = newID;
			ID_Source.Value = Convert.ToInt32(nodesource);
			ID_Target.Value = Convert.ToInt32(nodetarget);
			SQLcommand.ExecuteNonQuery();
			
			SQLcommand.CommandText = "UPDATE rtftable SET ID = @ID WHERE ID = @ID_Source";
			SQLcommand.Parameters.Add(ID);
			SQLcommand.Parameters.Add(ID_Source);
			ID.Value = newID;
			ID_Source.Value = Convert.ToInt32(nodesource);
			SQLcommand.ExecuteNonQuery();
			
			SQLcommand.CommandText = "UPDATE filenametable SET ID = @ID WHERE ID = @ID_Source";
			SQLcommand.Parameters.Add(ID);
			SQLcommand.Parameters.Add(ID_Source);
			ID.Value = newID;
			ID_Source.Value = Convert.ToInt32(nodesource);
			SQLcommand.ExecuteNonQuery();
			
			
			
			cnn.Close();	
			
		}
//-----добавление файла в БД для данного узла------------------------------------------------------
		bool addFile(string IDs, string FName)
			{
			int new_id_file = max_id_file() + 1;
			
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + db_file_name.Value);
     		cnn.Open();
     		
 			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
 			
			SQLiteParameter ID = new SQLiteParameter("@ID");
			SQLiteParameter ID_file_name = new SQLiteParameter("@ID_file_name");
			SQLiteParameter ID_file = new SQLiteParameter("@ID_file");
			SQLcommand.CommandText = "INSERT INTO filenametable (ID, ID_file, ID_file_name) VALUES(@ID, @ID_file, @ID_file_name)";
			SQLcommand.Parameters.Add(ID);
			SQLcommand.Parameters.Add(ID_file_name);
			SQLcommand.Parameters.Add(ID_file);
			ID.Value = Convert.ToInt32(IDs);
			ID_file_name.Value = Path.GetFileName(FName);
			ID_file.Value = new_id_file;
			SQLcommand.ExecuteNonQuery();
					
			SQLiteCommand SQLcommand2 = new SQLiteCommand(cnn);
 			
			SQLiteParameter file_data = new SQLiteParameter("@file_data");
			SQLcommand2.CommandText = "INSERT INTO filedatatable (ID_file, file_data) VALUES(@ID_file, @file_data)";
			SQLcommand2.Parameters.Add(file_data);
			SQLcommand2.Parameters.Add(ID_file);
							
			ID_file.Value = new_id_file;
			file_data.Value = File.ReadAllBytes (FName);
			
			SQLcommand2.ExecuteNonQuery();
			
			
			
			cnn.Close();
			return true;
			}
//------чтение файла из БД на диск-----------------------------------------------------------------
		void ReadFile (string ID_file)
		{
	
			
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + db_file_name.Value);
     		cnn.Open();
     		
 			SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
 			SQLiteParameter id_file = new SQLiteParameter("@id_file");
			SQLcommand.CommandText = "SELECT ID_file_name FROM filenametable WHERE ID_file = @id_file";
			SQLcommand.Parameters.Add(id_file);
			id_file.Value = ID_file;
			SQLiteDataReader read_data = SQLcommand.ExecuteReader ();
			read_data.Read ();
 			string FName = read_data.GetString(0);
 			
 			SQLiteCommand SQLcommand2 = new SQLiteCommand(cnn);
 			SQLcommand2.CommandText = "SELECT file_data FROM filedatatable WHERE ID_file = @id_file";
			SQLcommand2.Parameters.Add(id_file);
			id_file.Value = ID_file;
			SQLiteDataReader read_data2 = SQLcommand2.ExecuteReader ();
			read_data2.Read ();
			
			var blob = new Byte[(read_data2.GetBytes(0, 0, null, 0, int.MaxValue))];
	        read_data2.GetBytes(0, 0, blob, 0, blob.Length);
 	 		File.WriteAllBytes (FName, blob);
 				
 			MessageBox.Show ("File "+ FName + " save to disk");
 			cnn.Close();
			
			
		}
//------------Удаление файла из БД-----------------------------------------------------------------
		void DeleteFile (string ID_file)
		{
			SQLiteConnection cnn = new SQLiteConnection("Data Source = " + db_file_name.Value);
     		cnn.Open();
     		
     		
     		SQLiteCommand SQLcommand = new SQLiteCommand(cnn);
 			SQLiteParameter id_file = new SQLiteParameter("@ID_file");
 			SQLcommand.CommandText = "DELETE FROM filenametable WHERE ID_file = @ID_file";
			SQLcommand.Parameters.Add(id_file);
			id_file.Value = ID_file;
			SQLcommand.ExecuteNonQuery();
     		
			SQLcommand.CommandText = "DELETE FROM filedatatable WHERE ID_file = @ID_file";
			SQLcommand.Parameters.Add(id_file);
			id_file.Value = ID_file;
			SQLcommand.ExecuteNonQuery();
			cnn.Close();
				
		}
//-----------------------------------------------------------------------------
		
		
	}
	
//-----------------------------------------------------------------------------
			
}

